﻿Imports DataGridViewAutoFilter

Public Class DatacutsForm

    Dim SelectedRID As String
    Dim ChangesMade As Boolean = False
    Dim FireEvents As Boolean = False
    Dim IsFactors As Boolean = False
    Dim ShowMenu As Boolean = False
    Dim RecordCount As Integer
    Dim TheScenario As String = "<All>"
    Dim FormEntered As Boolean = False





    Private Sub DatacutsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        RefreshAll()

        If UserFormCheck(Me.Name) = "R" Then
            SaveButton.Enabled = False
            MessageBox.Show("Currently you only have read only access for the Datacuts section." + vbNewLine + "Create and edit functions have been disabled.", "Access Rights",
                            MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If

    End Sub

    Private Sub LoadTreeView(ByVal TheScenario As String)

        'Load Datacuts into Treeview
        Dim TableView As DataView
        Dim CutView As DataView
        Dim ANode As TreeNode
        Dim sql As String

        TableView = LoadDataView("Select * from Modelling_TableList")

        Dim Row As DataRowView
        Dim crow As DataRowView

        TreeView.Nodes.Clear()

        For Each Row In TableView

            ANode = TreeView.Nodes.Add(Row.Item("Table_Name").ToString, Row.Item("Table_Description").ToString, 0, 0)

            If TheScenario = "<All>" Then

                sql = "Select Distinct(DataCut_ID), Description from Modelling_" & Row.Item("Table_Description".ToString) & " " & _
                        "Where Source not like '%Case%' " & _
                        "Order by Description"
            Else

                sql = "Select a.DataCut_ID, Description From " & _
                            "(Select Table_Name, Datacut_ID From modelling_steps_products " & _
                            "Where Scenario_ID = '" & TheScenario & "' " & _
                            "And Table_Name = 'Modelling_" & Row.Item("Table_Description".ToString) & "' " & _
                            "And DataCut_ID not like '%Case%' " & _
                            "Union " & _
                            "Select Table_Name, Datacut_ID From modelling_steps " & _
                            "Where Scenario_ID = '" & TheScenario & "' " & _
                            "And Table_Name = 'Modelling_" & Row.Item("Table_Description".ToString) & "' " & _
                            "And DataCut_ID not like '%Case%') a " & _
                     "Inner Join DataCuts as b on a.DataCut_ID = b.DataCut_ID and a.Table_Name = b.Table_Name"

            End If

            Clipboard.Clear()
            Clipboard.SetText(sql)

            CutView = LoadDataView(sql)

            For Each crow In CutView
                ANode.Nodes.Add(crow.Item("DataCut_ID").ToString, crow.Item("Description").ToString, 1, 1)
            Next

        Next

    End Sub

    Private Sub TreeView_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView.AfterSelect

        If FireEvents = False Then Exit Sub

        GetData()
        LoadInfoView()

    End Sub

    Private Sub GetData()

        Dim sql As String
        Dim TheTable, TheDataCut_ID, TheDescription, TheBudget As String

        If TreeView.SelectedNode Is Nothing Then Exit Sub

        If TreeView.SelectedNode.Parent Is Nothing Then Exit Sub

        ShowLoadingForm()

        TheTable = Trim(TreeView.SelectedNode.Parent.Name.ToString)
        TheDataCut_ID = Trim(TreeView.SelectedNode.Name.ToString)
        TheDescription = Trim(TreeView.SelectedNode.Text.ToString)
        TheBudget = Trim(FilterBox.SelectedValue.ToString)

        If TheTable = "Modelling_Factors" Then
            IsFactors = True
        Else
            IsFactors = False
        End If


        If IsFactors = True Then

            sql = "Select a.UPIN as 'UPIN', a.Provider_Name as 'Provider', a.LA_Name as 'Local Authority', d.Budget_Description as [Budget], " & _
                   "a.RID as 'TheRID', '" & TheTable & "' as 'TheTable', '" & TheDataCut_ID & "' as 'TheDataCut_ID', '" & TheDescription & "' as 'TheDescription', " & _
                   "b.Programme_Weighting as [Programme Weighting], b.Area_Cost as [Area Cost], b.Disadvantage_Factor as [Disadvantage Factor], " & _
                   "b.Success_Factor as [Success Factor], b.Adjustment_Factor as [Adjustment Factor], b.Short_Course_Modifier as [Short Course Modifier], " & _
                   "b.Care_Standards_Factor as [Care Standards Factor], b.Long_Term_Residential_Factor as [Long Term Residential Factor], b.Value as [Provider Factor], " & _
                   "0 as 'Change' from All_Provider_Fields as a " & _
                   "Left Outer Join (select * from " & TheTable & " where DataCut_ID = '" & TheDataCut_ID & "' and Status = 'Approved') as b on a.RID = b.RID " & _
                    "Left Outer Join (select distinct UPIN, Budget_ID from Provider_Funding_Streams) as c on a.UPIN = c.UPIN " & _
                    "Left Outer Join Budgets as d on c.Budget_ID = d.Budget_ID " & _
                    "Where a.Lead = 1 and d.Budget_ID = '" & TheBudget & "' " & _
                   "Order by a.Provider_Name, a.LA_Name"
        Else

            sql = "Select a.UPIN as 'UPIN', a.Provider_Name as 'Provider', a.LA_Name as 'Local Authority', d.Budget_Description as [Budget], " & _
                    "a.RID as 'TheRID', '" & TheTable & "' as 'TheTable', '" & TheDataCut_ID & "' as 'TheDataCut_ID', '" & TheDescription & "' as 'TheDescription', " & _
                    "'' as [New Value], b.Value as [Current Value], 0 as 'Change' from All_Provider_Fields as a " & _
                    "Left Outer Join (select * from " & TheTable & " where DataCut_ID = '" & TheDataCut_ID & "' and Status = 'Approved') as b on a.RID = b.RID " & _
                    "Left Outer Join (select distinct UPIN, Budget_ID from Provider_Funding_Streams) as c on a.UPIN = c.UPIN " & _
                    "Left Outer Join Budgets as d on c.Budget_ID = d.Budget_ID " & _
                    "Where a.Lead = 1 and d.Budget_ID = '" & TheBudget & "' " & _
                    "Order by a.Provider_Name, a.LA_Name"
        End If

        Clipboard.Clear()
        Clipboard.SetText(sql)

        Try
            DataGridViewAutoFilterTextBoxColumn.RemoveFilter(GridView)
        Catch ex As Exception

        End Try

        GridView.AutoGenerateColumns = True
        GridView.Columns.Clear()

        TheData.DataSource = LoadDataTable(sql)
        GridView.DataSource = TheData

        Dim col As DataGridViewColumn

        For Each col In GridView.Columns
            col.HeaderCell = New DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell)

        Next

        'format columns
        With GridView

            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

            Dim c As DataGridViewColumn

            For Each c In .Columns
                Select Case c.HeaderText.ToString
                    Case "UPIN", "Provider", "Local Authority", "Budget"
                        c.Visible = True
                        c.ReadOnly = True
                        c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                    Case "Current Value", "Provider Factor"
                        c.Visible = True
                        c.ReadOnly = True
                        c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                        c.DefaultCellStyle.BackColor = Color.LightGray
                    Case "New Value"
                        c.Visible = True
                        c.ReadOnly = False
                        c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                    Case "Area Cost", "Programme Weighting", "Disadvantage Factor", "Short Course Modifier", "Long Term Residential Factor", _
                            "Care Standards Factor", "Adjustment Factor", "Success Factor"
                        c.Visible = True
                        c.ReadOnly = False
                        c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                    Case Else
                        c.Visible = False
                End Select

            Next

        End With

        RecordCount = TheData.Count
        RecordCountLabel.Text = "Description - " & TheDescription & "      Table - " & Mid(TheTable, 11, Len(TheTable)) & "       Records - " & RecordCount
        RecordCountLabel.Refresh()

        HideLoadingForm()

    End Sub

    Private Sub LoadInfoView()

        LoadInfo()

    End Sub

    Private Sub LoadInfo()

        Dim TheTable, DataCut_ID, sql As String
        Dim TheData As DataTable

        If TreeView.SelectedNode.Parent Is Nothing Then Exit Sub

        TheTable = Trim(TreeView.SelectedNode.Parent.Name.ToString)
        DataCut_ID = Trim(TreeView.SelectedNode.Name.ToString)

        sql = "select Distinct LTRIM(RTRIM(Scenario_Description)) as 'Scenario', LTRIM(RTRIM(Type)) as 'Type', LTRIM(RTRIM(Description)) as 'Description' from " & _
                "(Select Scenario_Description, Product_Description as 'Description', 'Product' as 'Type' from modelling_steps_products as a " & _
                "left outer join dbo.Modelling_Products as b on a.Scenario_ID = b.Scenario_ID and a.Product_Number = b.Product_Number " & _
                "left outer join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                "Where Table_Name = '" & TheTable & "' and a.DataCut_ID = '" & DataCut_ID & "' " & _
                " union " & _
                "Select Scenario_Description, Stage_Description as 'Description', 'Step' as 'Type' from modelling_steps as a " & _
                "left outer join dbo.Modelling_stages as b on a.Scenario_ID = b.Scenario_ID and a.Stage_Number = b.Stage_Number " & _
                "left outer join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                 "Where Table_Name = '" & TheTable & "' and a.DataCut_ID = '" & DataCut_ID & "') sq"

        'Clipboard.Clear()
        'Clipboard.SetText(sql)

        TheData = LoadDataTable(sql)

        With InfoView

            .DataSource = TheData
            .Refresh()
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

            If TheData.Rows.Count <> 0 Then
                InfoBox.Visible = False
            Else
                InfoBox.Visible = True
            End If

            Dim c As DataGridViewColumn

            For Each c In .Columns
                c.SortMode = DataGridViewColumnSortMode.NotSortable
            Next

        End With

    End Sub

    Private Sub GridView_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles GridView.CellFormatting

        With GridView

            If e.RowIndex >= 0 Then

                Try
                    If .Item("TheTable", e.RowIndex).Value.Contains("Rates") Then
                        .Columns("Current Value").DefaultCellStyle.Format = "£#,#"
                        .Columns("New Value").DefaultCellStyle.Format = "£#,#"
                    End If

                    If .Item("TheTable", e.RowIndex).Value.Contains("Funding") Then
                        .Columns("Current Value").DefaultCellStyle.Format = "£#,#"
                        .Columns("New Value").DefaultCellStyle.Format = "£#,#"
                    End If
                Catch ex As Exception
                    Exit Sub

                End Try


            End If
        End With

        With GridView
            If e.RowIndex <> -1 Then
                If .Columns(e.ColumnIndex).HeaderText = "New Value" Then
                    If .Item("Change", e.RowIndex).Value = 1 Then

                        .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.LightBlue
                    Else
                        .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.White
                    End If

                End If

                If IsFactors = True Then

                    Dim C As DataGridViewColumn

                    C = GridView.Columns(e.ColumnIndex)

                    Select Case C.HeaderText.ToString
                        Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", _
                            "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor"
                            If .Item("Change", e.RowIndex).Value = 1 Then

                                .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.LightBlue
                            Else
                                .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.White
                            End If
                        Case Else
                            'Do nothing
                    End Select

                End If
            End If
        End With

    End Sub

    Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteDataCutToolStripMenuItem.Click

        If MouseButtons.Right = True Then Exit Sub

        If MessageBox.Show("Deleting this entire datacut will delete it permanently from the database - are you sure you wish to continue?", _
                           "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        ShowLoadingForm()
        DeleteDatacut()

        HideLoadingForm()

        MessageBox.Show("The datacut has been removed from the database", "Datacut Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information)

        GridView.DataSource = Nothing
        RefreshAll()

    End Sub

    Private Sub DeleteDatacut()

        Dim Sql, DataCut_ID, TheTable As String

        TheTable = Trim(TreeView.SelectedNode.Parent.Name.ToString)
        DataCut_ID = Trim(TreeView.SelectedNode.Name.ToString)

        Sql = "Delete from " & TheTable & " where DataCut_ID = '" & DataCut_ID & "'"

        RunNonQuery(Sql)

    End Sub

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click

        If ChangesMade = False Then
            MessageBox.Show("No changes have been made to save", "No Changes Made", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        SaveData()
        GetData()

    End Sub

    Private Sub SaveData()

        Dim DataCut_ID, RID, SQL, TheValue, Table, Description As String
        Dim AC, SF, DF, PW, SCM, CSF, AF, LTRF As String
        Dim r As Integer
        DataGridViewAutoFilterTextBoxColumn.RemoveFilter(GridView)
        'Set these variables now as they will be the same for all records
        DataCut_ID = Trim(GridView.Item("TheDataCut_ID", 0).Value.ToString)
        Description = Trim(GridView.Item("TheDescription", 0).Value.ToString)
        Table = Trim(GridView.Item("TheTable", 0).Value.ToString)

        Dim TheSource As String = ""
        Dim SourceView As DataView

        If IsFactors = True Then

            For r = 0 To GridView.Rows.Count - 1

                TheValue = Trim(GridView.Item("Provider Factor", r).Value.ToString)

                If GridView.Item("Change", r).Value = 1 And TheValue <> "" Then

                    RID = Trim(GridView.Item("TheRID", r).Value.ToString)
                    PW = Trim(GridView.Item("Programme Weighting", r).Value.ToString)
                    AC = Trim(GridView.Item("Area Cost", r).Value.ToString)
                    SF = Trim(GridView.Item("Success Factor", r).Value.ToString)
                    DF = Trim(GridView.Item("Disadvantage Factor", r).Value.ToString)
                    AF = Trim(GridView.Item("Adjustment Factor", r).Value.ToString)
                    SCM = Trim(GridView.Item("Short Course Modifier", r).Value.ToString)
                    LTRF = Trim(GridView.Item("Long Term Residential Factor", r).Value.ToString)
                    CSF = Trim(GridView.Item("Care Standards Factor", r).Value.ToString)

                    SQL = "Delete from " & Table & " " & _
                            "Where RID = '" & RID & "' " & _
                            "And DataCut_ID = '" & DataCut_ID & "' " & _
                            "Insert into " & Table & " (Datacut_ID, RID, Source, Status, Description, Programme_Weighting, Area_Cost, " & _
                            "Success_Factor, Disadvantage_Factor, Adjustment_Factor, Short_Course_Modifier, Long_Term_Residential_Factor, " & _
                            "Care_Standards_Factor, Value) Values('" & DataCut_ID & "', '" & RID & "', 'User', 'Approved', '" & Description & _
                            "', '" & PW & "','" & AC & "','" & SF & "','" & DF & "','" & AF & "','" & SCM & "','" & LTRF & "','" & CSF & "','" & TheValue & "')"

                    'Clipboard.Clear()
                    'Clipboard.SetText(SQL)

                    RunNonQuery(SQL)

                    GridView.Item("Change", r).Value = 0

                End If

            Next

        Else

            For r = 0 To GridView.Rows.Count - 1

                If TheSource = "" Then
                    SQL = "Select top 1 Source from " & Table & " where DataCut_ID = '" & DataCut_ID & "'"
                    SourceView = LoadDataView(SQL)

                    If SourceView.Count = 0 Then
                        TheSource = "User"
                    Else
                        TheSource = SourceView(0).Item("Source").ToString
                    End If
                End If

                TheValue = Trim(GridView.Item("New Value", r).Value.ToString)

                If GridView.Item("Change", r).Value = 1 And TheValue <> "" Then

                    RID = Trim(GridView.Item("TheRID", r).Value.ToString)

                    SQL = "Delete from " & Table & " " & _
                            "Where RID = '" & RID & "' " & _
                            "And DataCut_ID = '" & DataCut_ID & "' " & _
                            "Insert into " & Table & " (Datacut_ID, RID, Source, Status, Description, Value) " & _
                            "Values('" & DataCut_ID & "', '" & RID & "', '" & TheSource & "', 'Approved', '" & Description & "', '" & TheValue & "')"

                    'Clipboard.Clear()
                    'Clipboard.SetText(SQL)

                    RunNonQuery(SQL)

                    GridView.Item("Change", r).Value = 0

                End If

            Next

        End If

        MessageBox.Show("Your changes have been saved", "Save Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)

        ChangesMade = False

    End Sub

    Private Sub GridView_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridView.CellValueChanged

        With GridView

            If .Columns(e.ColumnIndex).HeaderText = "New Value" Then
                If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> "" Then
                    ChangesMade = True
                    .Item("Change", e.RowIndex).Value = 1
                    .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.LightBlue
                Else
                    .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.White
                End If

            End If

            If IsFactors = True Then

                Dim C As DataGridViewColumn

                C = GridView.Columns(e.ColumnIndex)

                Select Case C.HeaderText.ToString
                    Case "Programme Weighting", "Area Cost", "Success Factor", "Disadvantage Factor", _
                        "Adjustment Factor", "Short Course Modifier", "Long Term Residential Factor", "Care Standards Factor"
                        If .Item(e.ColumnIndex, e.RowIndex).Value.ToString <> "" Then
                            ChangesMade = True
                            .Item("Change", e.RowIndex).Value = 1
                            .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.LightBlue
                        Else
                            .Item(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.White
                        End If
                    Case Else
                        'Do nothing
                End Select

            End If

        End With


        Dim Programme_Weighting As String = ""
        Dim Area_Cost As String = ""
        Dim Success_Factor As String = ""
        Dim Disadvantage_Factor As String = ""
        Dim Adjustment_Factor As String = ""
        Dim Short_Course_Modifier As String = ""
        Dim Long_Term_Residential_Factor As String = ""
        Dim Care_Standards_Factor As String = ""
        Dim TheValue As Decimal
        Dim r As Integer

        If IsFactors Then
            If GridView.Columns(e.ColumnIndex).Name = "Factors" Then Exit Sub

            'calculate the factor
            With GridView
                r = e.RowIndex

                Programme_Weighting = .Item(.Columns("Programme Weighting").Index, r).Value.ToString
                Area_Cost = .Item(.Columns("Area Cost").Index, r).Value.ToString
                Success_Factor = .Item(.Columns("Success Factor").Index, r).Value.ToString
                Disadvantage_Factor = .Item(.Columns("Disadvantage Factor").Index, r).Value.ToString
                Adjustment_Factor = .Item(.Columns("Adjustment Factor").Index, r).Value.ToString
                Short_Course_Modifier = .Item(.Columns("Short Course Modifier").Index, r).Value.ToString
                Long_Term_Residential_Factor = .Item(.Columns("Long Term Residential Factor").Index, r).Value.ToString
                Care_Standards_Factor = .Item(.Columns("Care Standards Factor").Index, r).Value.ToString

                If Programme_Weighting.Length = 0 Then Programme_Weighting = 0
                If Area_Cost.Length = 0 Then Area_Cost = 0
                If Success_Factor.Length = 0 Then Success_Factor = 0
                If Disadvantage_Factor.Length = 0 Then Disadvantage_Factor = 0
                If Adjustment_Factor.Length = 0 Then Adjustment_Factor = 0
                If Short_Course_Modifier.Length = 0 Then Short_Course_Modifier = 0
                If Long_Term_Residential_Factor.Length = 0 Then Long_Term_Residential_Factor = 0
                If Care_Standards_Factor.Length = 0 Then Care_Standards_Factor = 0

                TheValue = Programme_Weighting * Area_Cost * Success_Factor * Disadvantage_Factor * Adjustment_Factor * Short_Course_Modifier * Long_Term_Residential_Factor * Care_Standards_Factor
                .Item(.Columns("Provider Factor").Index, r).Value = TheValue
            End With
        End If

    End Sub

    Private Sub GridView_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles GridView.DataError

        MessageBox.Show("Only values can be saved in the database, ensure you have not entered any text", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Sub

    Private Sub GridView_CellValidating(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles GridView.CellValidating

        If IsFactors = True Then

            If FireEvents = False Then Exit Sub

            If e.FormattedValue = "" Then Exit Sub

            With GridView.Columns(e.ColumnIndex)

                'Capture input errors in the Provider Factor screen
                If .HeaderText.ToString.Contains("Success Factor") Or
                    .HeaderText.ToString.Contains("Area Cost") Or
                    .HeaderText.ToString.Contains("Programme Weighting") Or
                    .HeaderText.ToString.Contains("Short Course Modifier") Or
                    .HeaderText.ToString.Contains("Disadvantage Factor") Or
                    .HeaderText.ToString.Contains("Adjustment Factor") Or
                    .HeaderText.ToString.Contains("Care Standards Factor") Or
                    .HeaderText.ToString.Contains("Long Term Residential Factor") Then

                    Try
                        If e.FormattedValue <= 0 Or e.FormattedValue >= 2 Then
                            e.Cancel = True
                            MessageBox.Show("You must enter a valid provider factor element to 3 decimal places, greater than 0 but less than 2 (e.g. 1.234)", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                    Catch
                        'If c-razy text is entered
                        e.Cancel = True
                        MessageBox.Show("You must enter a valid provider factor element to 3 decimal places, greater than 0 but less than 2 (e.g. 1.234)", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try

                End If

            End With

        End If

    End Sub

    Private Sub GridView_RowValidating(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles GridView.RowValidating

        If FireEvents = False Then Exit Sub

        If IsFactors = False Then Exit Sub

        If GridView.RowCount <> RecordCount Then Exit Sub

        'If IsNothing(GridView.Item(GridView.Columns("Programme Weighting").Index, e.RowIndex)) Then Exit Sub


        Dim Programme_Weighting As String = ""
        Dim Area_Cost As String = ""
        Dim Success_Factor As String = ""
        Dim Disadvantage_Factor As String = ""
        Dim Adjustment_Factor As String = ""
        Dim Short_Course_Modifier As String = ""
        Dim Long_Term_Residential_Factor As String = ""
        Dim Care_Standards_Factor As String = ""
        Dim r As Integer
        Dim Provider_Factor As String

        With GridView

            Try
                Programme_Weighting = .Item(.Columns("Programme Weighting").Index, r).Value.ToString
                Area_Cost = .Item(.Columns("Area Cost").Index, r).Value.ToString
                Success_Factor = .Item(.Columns("Success Factor").Index, r).Value.ToString
                Disadvantage_Factor = .Item(.Columns("Disadvantage Factor").Index, r).Value.ToString
                Adjustment_Factor = .Item(.Columns("Adjustment Factor").Index, r).Value.ToString
                Short_Course_Modifier = .Item(.Columns("Short Course Modifier").Index, r).Value.ToString
                Long_Term_Residential_Factor = .Item(.Columns("Long Term Residential Factor").Index, r).Value.ToString
                Care_Standards_Factor = .Item(.Columns("Care Standards Factor").Index, r).Value.ToString
                Provider_Factor = .Item(.Columns("Provider Factor").Index, r).Value.ToString

                'If entrie row is blank then exit sub
                If Programme_Weighting.Length = 0 And
                   Area_Cost.Length = 0 And
                   Success_Factor.Length = 0 And
                   Disadvantage_Factor.Length = 0 And
                   Adjustment_Factor.Length = 0 And
                   Short_Course_Modifier.Length = 0 And
                   Long_Term_Residential_Factor.Length = 0 And
                   Care_Standards_Factor.Length = 0 Then
                    Exit Sub
                End If

                'If user has started to edit row then 
                If Programme_Weighting.Length = 0 Or
                    Area_Cost.Length = 0 Or
                    Success_Factor.Length = 0 Or
                    Disadvantage_Factor.Length = 0 Or
                    Adjustment_Factor.Length = 0 Or
                    Short_Course_Modifier.Length = 0 Or
                    Long_Term_Residential_Factor.Length = 0 Or
                    Care_Standards_Factor.Length = 0 Then

                    MessageBox.Show("You have started to edit a provider factor but have not entered values for all the elements. Either add all the required values or delete all existing ones for the provider.", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    e.Cancel = True
                    Exit Sub
                End If

                If Provider_Factor <= 0 Or Provider_Factor >= 2 Then

                    MessageBox.Show("Your overall Provider Factor must be greater than 0 but less than 2 (e.g. 1.234)", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    e.Cancel = True
                End If
            Catch ex As Exception
                Exit Sub
            End Try

        End With


    End Sub

    Private Sub TreeView_BeforeSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewCancelEventArgs) Handles TreeView.BeforeSelect

        If FormEntered = True Then
            FormEntered = False
            Exit Sub
        End If

        If ChangesMade = False Then Exit Sub

        If MessageBox.Show("You have made changes to this data, do you want to save before exiting?", "Changes Made", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            SaveData()
        Else
            ChangesMade = False
        End If


    End Sub

    Private Sub DatacutsForm_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Leave

        FormEntered = True
        FireEvents = False

    End Sub

    Private Sub TreeView_MouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TreeView.MouseUp

        'Just to be sure...
        If e.Button = MouseButtons.Left Then
            TreeView.ContextMenuStrip.Visible = False
            Exit Sub
        End If

        If e.Button = MouseButtons.Right Then

            Dim P As Point = New Point(e.X, e.Y)
            Dim TheSelectedNode As TreeNode = TreeView.GetNodeAt(P)

            TreeView.SelectedNode = TheSelectedNode

            'If no node is clicked
            If TreeView.SelectedNode Is Nothing Then
                TreeView.ContextMenuStrip.Visible = False
                Exit Sub
            End If

            'Don't show context menu on parent nodes
            If TreeView.SelectedNode.Parent Is Nothing Then
                TreeView.ContextMenuStrip.Visible = False
                Exit Sub
            End If

            'Else Build then menu....
            Dim TheData As DataView
            Dim TheTable, TheDataCut, sql As String

            TheDataCut = TreeView.SelectedNode.Name.ToString
            TheTable = TreeView.SelectedNode.Parent.Name.ToString

            sql = "Select top 1 * from " & TheTable & " where DataCut_ID = '" & TheDataCut & "'"

            TheData = LoadDataView(sql)

            Dim Row As DataRowView
            For Each Row In TheData
                If Row.Item("Source").ToString.Contains("DCC") Then
                    DeleteDataCutToolStripMenuItem.Enabled = True
                    BuildDatacutMenu()
                Else
                    DeleteDataCutToolStripMenuItem.Enabled = False
                    'ToolStripSeparator1.Visible = False
                    BuildDatacutMenu()
                End If
            Next

        End If

    End Sub

    Private Sub GridView_CellContextMenuStripNeeded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellContextMenuStripNeededEventArgs) Handles GridView.CellContextMenuStripNeeded

        ShowMenu = False

        If e.RowIndex = -1 Then
            Exit Sub
        End If

        If e.ColumnIndex = -1 Then
            Exit Sub
        End If

        If GridView.Columns(e.ColumnIndex).HeaderText = "Current Value" Then
            ShowMenu = True
            GridView.CurrentCell = GridView.Item(e.ColumnIndex, e.RowIndex)
        End If

        If GridView.Columns(e.ColumnIndex).HeaderText = "Provider Factor" Then
            ShowMenu = True
            GridView.CurrentCell = GridView.Item(e.ColumnIndex, e.RowIndex)
        End If

    End Sub

    Private Sub DeleteData()

        Dim Sql, DataCut_ID, TheTable, TheRID As String

        With GridView

            TheTable = Trim(.Item("TheTable", .CurrentCell.RowIndex).Value.ToString)
            DataCut_ID = Trim(.Item("TheDataCut_ID", .CurrentCell.RowIndex).Value.ToString)
            TheRID = Trim(.Item("TheRID", .CurrentCell.RowIndex).Value.ToString)

            Sql = "Delete from " & TheTable & " where DataCut_ID = '" & DataCut_ID & "' and RID = '" & TheRID & "'"

            'Clipboard.Clear()
            'Clipboard.SetText(Sql)

            RunNonQuery(Sql)

        End With

    End Sub


    Private Sub DeleteDataToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteDataToolStripMenuItem1.Click

        If MouseButtons.Right = True Then Exit Sub

        If MessageBox.Show("Deleting this data will remove it permanently from the database - are you sure you wish to continue?", _
                           "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        DeleteData()
        GetData()
        MessageBox.Show("The data has been removed from the database", "Data Deleted", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub GridMenuStrip_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles GridMenuStrip.Opening

        e.Cancel = Not ShowMenu

    End Sub

    Private Sub ClearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearButton.Click

        If GridView.RowCount = 0 Then Exit Sub

        Try
            DataGridViewAutoFilterTextBoxColumn.RemoveFilter(GridView)
        Catch ex As Exception

        End Try

    End Sub

    Private Sub LoadScenarioBox()

        Dim sql As String

        sql = "Select * From " & _
                "(Select Scenario_ID as TheID, Scenario_Description as TheDescription From Modelling_Scenarios " & _
                "Union " & _
                "Select '<All>' as TheID, '<All Scenarios>' as TheDescription) sq " & _
              "Order By TheDescription"

        ScenarioBox.DataSource = LoadDataView(sql)
        ScenarioBox.ValueMember = "TheID"
        ScenarioBox.DisplayMember = "TheDescription"
        ScenarioBox.DropDownStyle = ComboBoxStyle.DropDownList
        ScenarioBox.Refresh()

    End Sub

    Private Sub ScenarioBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScenarioBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        If ChangesMade = True Then

            If MessageBox.Show("You have made changes to the datacuts data, do you want to save before changing the scenario?", "Changes Made", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                SaveData()
            Else
                ChangesMade = False
            End If

        End If

        TheScenario = Trim(ScenarioBox.SelectedValue.ToString)
        LoadTreeView(TheScenario)

    End Sub


    Private Sub RefreshButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RefreshButton.Click

        If ChangesMade = True Then

            If MessageBox.Show("You have made changes to the datacuts data, do you want to save before refreshing?", "Changes Made", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                SaveData()
            Else
                ChangesMade = False
            End If

        End If

        RefreshAll()

    End Sub

    Private Sub FilterBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FilterBox.SelectedIndexChanged

        If FireEvents = False Then Exit Sub

        If ChangesMade = True Then

            If MessageBox.Show("You have made changes to the datacuts data, do you want to save before changing your filter?", "Changes Made", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                SaveData()
            Else
                ChangesMade = False
            End If

        End If

        GetData()

    End Sub

    Private Sub LoadFilterBox()

        Dim TheData As DataView

        TheData = LoadDataView("Select * from Budgets")

        FilterBox.DataSource = TheData
        FilterBox.ValueMember = "Budget_ID"
        FilterBox.DisplayMember = "Budget_Description"
        FilterBox.DropDownStyle = ComboBoxStyle.DropDownList
        FilterBox.Refresh()

    End Sub

    Private Sub DatacutsForm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

        If ChangesMade = False Then Exit Sub

        If MessageBox.Show("You have made changes to the datacuts page, do you want to save before exiting?", "Changes Made", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            SaveData()
        Else
            ChangesMade = False
        End If
    End Sub


    Sub BuildDatacutMenu()

        Dim TheDataCut, TheTable, sql As String

        Dim TheData As DataView
        Dim TheName As String

        TheDataCut = TreeView.SelectedNode.Name.ToString
        TheTable = TreeView.SelectedNode.Parent.Name.ToString

        If TheScenario = "<All>" Then

            sql = "Select Description from " & _
                     "(Select LTRIM(RTRIM(c.Scenario_Description)) + ' - Product - ' + b.Product_Description as Description from modelling_steps_products as a " & _
                     "inner join modelling_products as b on a.Product_Number = b.Product_Number and a.Scenario_ID = b.Scenario_ID " & _
                     "inner join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                     "where a.Datacut_ID = '" & TheDataCut & "' " & _
                     "and a.Table_Name = '" & TheTable & "' " & _
                       "Union " & _
                     "Select LTRIM(RTRIM(c.Scenario_Description)) + ' - Stage - ' + b.Stage_Description as Description from modelling_steps as a " & _
                     "inner join modelling_stages as b on a.stage_Number = b.stage_number and a.Scenario_ID = b.Scenario_ID " & _
                     "inner join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                     "where a.Datacut_ID = '" & TheDataCut & "' " & _
                     "and a.Table_Name = '" & TheTable & "') sq "

        Else

            sql = "Select Description from " & _
                "(Select LTRIM(RTRIM(c.Scenario_Description)) + ' - Product - ' + b.Product_Description as Description from modelling_steps_products as a " & _
                "inner join modelling_products as b on a.Product_Number = b.Product_Number and a.Scenario_ID = b.Scenario_ID " & _
                "inner join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                "where a.Datacut_ID = '" & TheDataCut & "' " & _
                "and a.Table_Name = '" & TheTable & "' " & _
                "and a.Scenario_ID = '" & TheScenario & "' " & _
                     "Union " & _
                "Select LTRIM(RTRIM(c.Scenario_Description)) + ' - Stage - ' + b.Stage_Description as Description from modelling_steps as a " & _
                "inner join modelling_stages as b on a.stage_Number = b.stage_number and a.Scenario_ID = b.Scenario_ID " & _
                "inner join modelling_scenarios as c on a.Scenario_ID = c.Scenario_ID " & _
                "where a.Datacut_ID = '" & TheDataCut & "' " & _
                "and a.Scenario_ID = '" & TheScenario & "' " & _
                "and a.Table_Name = '" & TheTable & "') sq "
        End If

        TheData = LoadDataView(sql)

        DatacutUsesMenuItem.DropDownItems.Clear()

        If TheData.Count = 0 Then

            DatacutUsesMenuItem.Enabled = False

        Else

            DatacutUsesMenuItem.Enabled = True

            Dim r As DataRowView

            For Each r In TheData
                TheName = r.Item("Description").ToString
                Dim MenuItem As New ToolStripMenuItem
                MenuItem.Name = TheName
                MenuItem.Text = TheName
                MenuItem.Image = TheImages.Images.Item(2)
                RemoveHandler (MenuItem.Click), AddressOf MenuItemClickEvent
                AddHandler (MenuItem.Click), AddressOf MenuItemClickEvent
                MenuItem.Enabled = True
                DatacutUsesMenuItem.DropDownItems.Add(MenuItem)
            Next

        End If

    End Sub

    Private Sub MenuItemClickEvent(ByVal sender As Object, ByVal e As EventArgs)
        'do what you need to do with the version selected here
    End Sub

    Private Sub RenameDatacutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RenameDatacutToolStripMenuItem.Click

        RenameDatacutForm.Visible = True

    End Sub

    Private Sub DatacutsForm_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Enter

        FireEvents = True

    End Sub

    Private Sub RefreshAll()

        LoadScenarioBox()
        LoadFilterBox()
        FireEvents = True
        LoadTreeView(TheScenario)

    End Sub

End Class